package com.quanyue.haircut.order.mapper;

import com.quanyue.haircut.domain.entity.ReserveOrder;
import com.quanyue.haircut.order.dto.ArriveShopCount;
import com.quanyue.haircut.order.dto.ReserveOrderDetail;
import com.quanyue.haircut.order.dto.TurnoverResult;
import com.quanyue.haircut.order.dto.UserByConsume;
import com.vhuomai.mybatis.mapper.auto.AutoMapper;
import com.vhuomai.mybatis.mapper.page.Page;
import com.vhuomai.mybatis.mapper.page.PageRequest;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;

import java.math.BigDecimal;
import java.time.LocalDate;
import java.util.List;

@Mapper
public interface ReserveOrderMapper extends AutoMapper <ReserveOrder, Integer> {
    ReserveOrder findByOrderNo(@Param("orderNo") String orderNo);

    @Select("<script>select o.order_no, o.pay_price, o.discount_price, o.create_time, o.pay_channel, r.phone, r.user_name, r.server_content, r.technician, "
            + "t.technician_name, t.technician_image, t.technician_grade, t.technician_phone, s.shop_name "
            + "from qy_reserve_order o "
            + "left join qy_shop s on o.shop_id = s.id "
            + "left join qy_reserve_info r on o.order_info = r.id "
            + "left join qy_technician t on r.technician = t.id "
            + "where o.order_status = 'DONE' <if test = \"userId == null\"> and o.shop_id = ${shopId} </if> "
            + "<if test = \"shopId == 0\"> and o.user_id = ${userId} </if></script> ")
    List <ReserveOrderDetail> findOrderList(@Param("userId") Integer userId, @Param("shopId") Integer shopId);

    @Select("SELECT sum(o.pay_price) AS consume_price,count(*) AS consume_count,u.id,u.sex,u.nick_name,u.phone,u.head_image,n.integral,c.balance,c.id as card_id,c.title "
            + "FROM qy_reserve_order o "
            + "RIGHT JOIN qy_user u on o.user_id = u.id "
            + "LEFT JOIN qy_user_integral n ON n.user_id = u.id "
            + "LEFT JOIN qy_card c ON c.user_id = u.id and c.del_flag = 0 "
            + "WHERE o.order_status = 'DONE' and o.shop_id = #{shopId} "
            + "GROUP BY o.user_id "
            + "HAVING consume_price >= #{consumePrice} AND consume_count >= #{consumeCount} ")
    Page <UserByConsume> findVipByConsume(@Param("shopId") Integer shopId,
                                          @Param("consumeCount") Integer consumeCount,
                                          @Param("consumePrice") BigDecimal consumePrice,
                                          @Param("request") PageRequest request);

    @Select("SELECT u.id,u.nick_name,u.phone,u.head_image,u.sex,n.integral,c.balance,c.id as card_id, c.title "
            + "from qy_card c "
            + "LEFT JOIN qy_user u ON c.user_id = u.id "
            + "LEFT JOIN qy_user_integral n ON n.user_id = u.id "
            + "WHERE c.shop_id = #{shopId} and c.del_flag = 0")
    Page <UserByConsume> findVipByShop(@Param("shopId") Integer shopId,
                                       @Param("request") PageRequest request);

    @Select("select DATE_FORMAT(s.create_time, '%Y-%m-%d') as date, sum(s.pay_price) as total "
            + "from qy_reserve_order s "
            + "where s.merchant_no = #{merchantNo}  and s.order_status = 'DONE' and DATE_FORMAT(s.create_time, '%Y-%m-%d') >= #{startDate} "
            + "and DATE_FORMAT(s.create_time, '%Y-%m-%d') <= #{endDate} "
            + "group by DATE_FORMAT(s.create_time, '%Y-%m-%d') "
            + "order by total desc")
    List <TurnoverResult> findDateTurnover(@Param("merchantNo") Integer merchantNo, @Param("startDate") LocalDate startDate, @Param("endDate") LocalDate endDate);

    @Select("select DATE_FORMAT(s.create_time, '%Y-%m') as date, sum(s.pay_price) as total "
            + "from qy_reserve_order s "
            + "where s.merchant_no = #{merchantNo} and s.order_status = 'DONE' and DATE_FORMAT(s.create_time, '%Y-%m') >= #{startMonth} "
            + "and DATE_FORMAT(s.create_time, '%Y-%m') <= #{endMonth} "
            + "group by DATE_FORMAT(s.create_time, '%Y-%m') "
            + "order by total desc")
    List <TurnoverResult> findMonthTurnover(@Param("merchantNo") Integer merchantNo, @Param("startMonth") String startMonth, @Param("endMonth") String endMonth);

    @Select("select sum(o.pay_price) from qy_reserve_order o where o.order_status = 'DONE' and o.merchant_no = #{merchantNo}")
    BigDecimal findTurnoverByMerchant(@Param("merchantNo") Integer merchantNo);

    @Select("select sum(o.pay_price) from qy_reserve_order o where o.merchant_no = #{merchantNo} "
            + "and o.order_status = 'DONE' and DATE_FORMAT(o.create_time, '%Y-%m') = #{now}")
    BigDecimal findTurnoverNow(@Param("merchantNo") Integer merchantNo, @Param("now") String now);

    @Select("select o.shop_name, count(*) as arrive_count "
            + "from qy_reserve_order s "
            + "left join qy_shop o on o.id = s.shop_id "
            + "where s.merchant_no = #{merchantNo} and s.order_status = 'DONE' and DATE_FORMAT(s.create_time, '%Y-%m-%d') >= #{startDate} "
            + "and DATE_FORMAT(s.create_time, '%Y-%m-%d') <= #{endDate} "
            + "group by s.shop_id "
            + "order by arrive_count desc")
    List <ArriveShopCount> findDateArriveShop(@Param("merchantNo") Integer merchantNo, @Param("startDate") LocalDate startDate, @Param("endDate") LocalDate endDate);


    @Select("select o.shop_name, count(*) as arrive_count "
            + "from qy_reserve_order s "
            + "left join qy_shop o on o.id = s.shop_id "
            + "where s.merchant_no = #{merchantNo} and s.order_status = 'DONE' and DATE_FORMAT(s.create_time, '%Y-%m') >= #{startMonth} "
            + "and DATE_FORMAT(s.create_time, '%Y-%m') <= #{endMonth} "
            + "group by s.shop_id "
            + "order by arrive_count desc")
    List <ArriveShopCount> findMonthArriveShop(@Param("merchantNo") Integer merchantNo, @Param("startMonth") String startMonth, @Param("endMonth") String endMonth);
}
